import pandas as pd
from sqlalchemy import create_engine

# MySQL的用户：root, 密码:123456, 端口：3306,数据库：iwgame
engine = create_engine('mysql+pymysql://dev:dev2012@10.2.7.133:3306/atest')#可以不加数据库名称

# 查询语句，选出employee表中的所有数据
sql = "select * from pay_dict_2;"

# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)

# 输出employee表的查询结果
print(df)

# **********************************************************************
"""
# 基于xlsxwriter模块，MySQL导出xlsx文件，自动格式化会存在科学计数法的数字
"""

import pymysql
import datetime
import xlsxwriter


def Get_Conn_Config(Data_Key):
    "获取数据库配置信息"
    Data_Config = {
        "host": "",
        "user": "",
        "password": "",
        "database": "",
        'port': 3306,
        'charset': 'utf8'
    }

    if Data_Key == "1":
        Data_Config["host"] = "127.0.0.1"
        Data_Config["user"] = "root"
        Data_Config["password"] = "password"
        Data_Config["database"] = "database"
    elif Data_Key == "2":
        Data_Config["host"] = "10.2.7.133"
        Data_Config["user"] = "dev"
        Data_Config["password"] = "dev2012"
        Data_Config["database"] = "atest"
    return Data_Config


def Export_MySQL_To_Excel(Export_File, SQL, MySQL_Conn_Config, DataBase=None, Out_Name=None):
    "导出数据库"
    try:
        db = pymysql.connect(**MySQL_Conn_Config)  # 获取mysql句柄
    except Exception as e:
        print("MySQL Connection Error!" + e)
        return
    cursor = db.cursor()  # 获取游标
    if Out_Name is None:
        file_path = input("请输入文件名(勿输入后缀名):")
    else:
        file_path = Out_Name
    print("查询中...")
    Start_time = datetime.datetime.now()
    try:
        if DataBase is not None: cursor.execute("use `%s`" % DataBase)
        cursor.execute(SQL)  # 执行SQL语句
    except Exception as e:
        print(e)
        return
    End_Time = datetime.datetime.now()
    print('耗时:%ds' % ((End_Time - Start_time).seconds))
    Row_Count = cursor.rowcount  # 取总行数
    Field_Name_Attr = cursor.description  # 取字段名
    Field_Name = [list[0] for list in Field_Name_Attr]
    print(Field_Name)  # 取字段名二维数组的第一列
    # print(Field_Name_Attr)

    Start_time = datetime.datetime.now()  # 取任务开始时间

    Separate_Count = 1000000  # 100W条数据自动分隔文件
    Is_Separate = False
    if Row_Count > Separate_Count:  # 总行数大于分隔行数，则启动分隔模式
        Is_Separate = True

    Separate = Row_Count // Separate_Count  # 分隔次数
    if Row_Count % Separate_Count != 0:  # 具有余数则分隔次数再加1次
        Separate += 1

    print("结果:%d" % Row_Count)
    print("分段:%d" % Separate)
    Separate += 1  # range函数从1开始的额外计算1次
    for Separate_Number in (range(1, Separate)):
        File_Name = Export_File + "\\" + file_path

        if Is_Separate == False:
            File_Name2 = File_Name + '.xlsx'
            wb = xlsxwriter.Workbook(File_Name2)
        else:
            File_Name2 = File_Name + "_" + str(Separate_Number) + '.xlsx'
            wb = xlsxwriter.Workbook(File_Name2)

        ws = wb.add_worksheet()
        # 标题样式：粗体 背景色 边框 字体颜色
        Title_Style = wb.add_format({'bold': True, 'fg_color': '#336666', 'border': 1, 'color': '#FFFFFF'})
        ws.write_row(0, 0, Field_Name, Title_Style)  # 写入标题

        tem_i = 0
        for Row_Number in range(Row_Count):
            data = cursor.fetchone()  # 读取一行数据
            if not data: break  # 如果没数据则跳出循环
            data2 = []
            col_ = 0
            for var in (data):
                # 格式化会科学计数法的数字型 type:5=double 8=bigint
                if Field_Name_Attr[col_][1] in (5, 8):
                    if var is not None:
                        if var > 9999999999:
                            # 当数字大于一定值才会科学计数法显示，将其格式化
                            data2.append(str(var))
                        else:
                            data2.append(var)
                else:
                    data2.append(var)
                col_ += 1
            ws.write_row(Row_Number + 1, 0, data2)  # 写入一行数据至Excel

            # 奢华的计算进度条
            Complete_Number = Row_Number + 1 + (Separate_Number - 1) * Separate_Count
            percentage = round(Complete_Number / Row_Count * 100)
            if Complete_Number >= Row_Count / 100 * tem_i:
                End_Time = datetime.datetime.now()
                print(
                    '\r任务:' + str(Separate_Number) + '/' + str(Separate - 1) + '[' + '■' * (percentage // 5) + '□' * (
                            20 - percentage // 5) + ']' + str(percentage) + '%,' + "%dS" % (
                        (End_Time - Start_time).seconds), end='')
                tem_i += 1
            # print("任务:%d/%d" % (Separate_Number, Separate - 1))

            if (Row_Number + 1) % Separate_Count == 0:  # 导出指定行数则进行分隔
                break

        DateTime_bold = wb.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'})
        Date_bold = wb.add_format({'num_format': 'yyyy-mm-dd'})
        Text_bold = wb.add_format({'num_format': '@'})

        # 格式化时间型的数据列 Type:7=Timestamp 10=Date 11=Time 12=DateTime
        for col_ in range(len(Field_Name_Attr)):
            if Field_Name_Attr[col_][1] in (7, 11, 12):
                ws.set_column(col_, col_, 20, DateTime_bold)  # yyyy-mm-dd hh:mm:ss
            elif Field_Name_Attr[col_][1] == 10:
                ws.set_column(col_, col_, 20, Date_bold)  # yyyy-mm-dd

        print()
        End_Time = datetime.datetime.now()
        print("保存中...%s,%ds" % (File_Name2, (End_Time - Start_time).seconds))
        wb.close()

    End_Time = datetime.datetime.now()
    print('耗时:%ds' % ((End_Time - Start_time).seconds))
    cursor.close()  # 关闭游标
    db.close()  # 断开MySQL链接


if __name__ == '__main__':
    # 将user表导出
    Export_MySQL_To_Excel("D:\\", "select * from t_misclog_actor_day", Get_Conn_Config("2"), "adcollect_sm")


# **************************************************
"""30行代码，低内存占用解读超大csv文件"""
import shlex
import datetime

if __name__ == "__main__":
    File_All_Path = r"D:\1000W行.csv"  # 以二进制方式读取文件
    File_ID = open(File_All_Path, "rb")
    LineNumber = 0  # 创建行号变量
    Read_Title = False  # 创建是否读标题变量
    Start_Time = datetime.datetime.now()  # 计时开始
    Line_Decode = "GBK"  # 设置编码
    while 1:
        Line_Byte = File_ID.readline()  # 读入一行二进制内容
        if not Line_Byte: break  # 判断二进制文本是否为空，为空则到文件尾部，跳出循环
        RowTest = Line_Byte.decode(Line_Decode).rstrip()  # 编码转换，并去除尾部的换行符号
        LineNumber += 1  # 行号变量自增
        if LineNumber == 1 and Read_Title == False: continue  # 是否读取第1行标题
        if LineNumber == 1000000:  # 读取第100W行内容
            str = shlex.shlex(RowTest, posix=True)  # 解析行内容数据，以逗号分割，不分割引号内容中的逗号
            str.whitespace = ','
            str.whitesapce_split = True
            ListTest = list(str)
            print(LineNumber, ListTest)
    End_Time = datetime.datetime.now()
    print("耗时:", End_Time - Start_Time)
    File_ID.close()


